package org.example.dao;

import org.example.entity.Employee;
import org.example.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;

public class EmployeeDAO {
    public static String getLastEmpCode(String departId) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "SELECT emp_code FROM ims_employee WHERE dpt_id = ? AND is_deleted = 0 ORDER BY emp_code DESC LIMIT 0,1";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, departId);
            ResultSet res = preparedStatement.executeQuery();
            if (res.next()){
                return res.getString("emp_code");
            }else {
                return "";
            }

        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return null;
        }
    }

    public static boolean addEmployee(Employee employee) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "INSERT INTO ims_employee (emp_id,dpt_id,emp_name,emp_code,emp_sex,create_time,update_time) VALUES (?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, employee.getEmpId());
            preparedStatement.setString(2, employee.getDptId());
            preparedStatement.setString(3, employee.getEmpName());
            preparedStatement.setString(4, employee.getEmpCode());
            preparedStatement.setString(5, employee.getEmpSex());
            preparedStatement.setObject(6, new java.sql.Timestamp(new java.util.Date().getTime()));
            preparedStatement.setObject(7, new java.sql.Timestamp(new java.util.Date().getTime()));

            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }

    public static ArrayList<String[]> getEmployeeInfo() {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "SELECT emp.emp_id,emp.emp_sex,emp.emp_code,dep.dpt_name,emp.emp_name FROM ims_employee emp LEFT JOIN ims_department dep ON emp.dpt_id = dep.dpt_id AND dep.is_deleted = 0 WHERE emp.is_deleted = 0 ";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ArrayList<String[]> resData = new ArrayList<>();

            while (resultSet.next()){
                String[] dataColumnList = new String[5];
                dataColumnList[0] = resultSet.getString("emp_id");
                if ("MALE".equals(resultSet.getString("emp_sex"))){
                    dataColumnList[1] = "男";
                } else if ("FEMALE".equals(resultSet.getString("emp_sex"))) {
                    dataColumnList[1] = "女";
                }else {
                    dataColumnList[1] = "暂无";
                }
                dataColumnList[2] = resultSet.getString("emp_code");
                if (resultSet.getString("dpt_name") == null){
                    dataColumnList[3] = "暂无";
                }else {
                    dataColumnList[3] = resultSet.getString("dpt_name");
                }

                dataColumnList[4] = resultSet.getString("emp_name");
                resData.add(dataColumnList);
            }
            return resData;
        } catch (SQLException e) {
            System.out.println("出错！请稍后再试："+e.getMessage());
            return null;
        }
    }

    public static boolean updateEmployee(String empNewName, String empNewSex, String empId) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "UPDATE ims_employee SET emp_name = ?,emp_sex = ?,update_time = ? WHERE emp_id = ? AND is_deleted = 0";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, empNewName);
            preparedStatement.setString(2, empNewSex);
            preparedStatement.setObject(3, new java.sql.Timestamp(new java.util.Date().getTime()));
            preparedStatement.setString(4, empId);

            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }

    public static boolean deleteEmployee(String id) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "UPDATE ims_employee SET is_deleted = 1 WHERE emp_id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);

            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }

    public static boolean updateEmployeeEmpCode(String newEmpCode, String id) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "UPDATE ims_employee SET emp_code = ? WHERE dpt_id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newEmpCode);
            preparedStatement.setString(2, id);
            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }
}
